package com.smartwebx.ha.dal.realtime.sqlprovider;

import static org.apache.ibatis.jdbc.SqlBuilder.BEGIN;
import static org.apache.ibatis.jdbc.SqlBuilder.FROM;
import static org.apache.ibatis.jdbc.SqlBuilder.GROUP_BY;
import static org.apache.ibatis.jdbc.SqlBuilder.INNER_JOIN;
import static org.apache.ibatis.jdbc.SqlBuilder.LEFT_OUTER_JOIN;
import static org.apache.ibatis.jdbc.SqlBuilder.ORDER_BY;
import static org.apache.ibatis.jdbc.SqlBuilder.SELECT;
import static org.apache.ibatis.jdbc.SqlBuilder.SQL;
import static org.apache.ibatis.jdbc.SqlBuilder.WHERE;

import java.util.Calendar;
import java.util.Date;
import java.util.Map;

import org.apache.commons.lang3.time.DateUtils;

import com.smartwebx.ha.dal.config.OracleConfigInfo;

public class WaterProvider {
	private enum DataType {
		/**
		 * 河道GIS
		 */
		RiverGis,
		/**
		 * 水库GIS
		 */
		ReservoirGis,
		/**
		 * 堰闸GIS
		 */
		WeirGatesGis,
		/**
		 * 泵站GIS
		 */
		PumpingStationGis,
		/**
		 * 潮位GIS
		 */
		TideGis
	}
	private static final String ST_HJ_B = OracleConfigInfo.REALTIME_NAME_HJANDKJ + ".ST_HJ_B";
	private static final String ST_KJ_B = OracleConfigInfo.REALTIME_NAME_HJANDKJ + ".ST_KJ_B";
	private static final String ST_RVFCCH_B = OracleConfigInfo.REALTIME_NAME + ".ST_RVFCCH_B";
	private static final String ST_RSVRFSR_B = OracleConfigInfo.REALTIME_NAME + ".ST_RSVRFSR_B";
	
	
	private static final String ST_RIVER_R = OracleConfigInfo.REALTIME_NAME
			+ ".ST_RIVER_R";
	private static final String ST_RSVR_R = OracleConfigInfo.REALTIME_NAME
			+ ".ST_RSVR_R";
	private static final String ST_WAS_R = OracleConfigInfo.REALTIME_NAME
			+ ".ST_WAS_R";
	private static final String ST_PUMP_R = OracleConfigInfo.REALTIME_NAME
			+ ".ST_PUMP_R";
	private static final String ST_TIDE_R = OracleConfigInfo.REALTIME_NAME
			+ ".ST_TIDE_R";
	private static final String ST_STBPRP_B = OracleConfigInfo.REALTIME_NAME
			+ ".ST_STBPRP_B";

	public String getLatestRiverGisInfoSQL(Map<String, Date> maps) {
		maps = this.reParams(maps);
		
		BEGIN();
		String primaryTableSql = primaryTableBuild(DataType.RiverGis,maps);
		String slaveTableSql = slaveTableBuild(DataType.RiverGis);
		SELECT(columnToFieldMap(DataType.RiverGis));
		FROM("(" + primaryTableSql + ") Z");
		INNER_JOIN("(" + slaveTableSql + ") T on Z.STCD=T.STCD");
		return SQL();
	}

	public String getLatestReservoirGisInfoSQL(Map<String, Date> maps) {
		maps = this.reParams(maps);
		
		BEGIN();
		String primaryTableSql = primaryTableBuild(DataType.ReservoirGis,maps);
		String slaveTableSql = slaveTableBuild(DataType.ReservoirGis);
		SELECT(columnToFieldMap(DataType.ReservoirGis));
		FROM("(" + primaryTableSql + ") Z");
		INNER_JOIN("(" + slaveTableSql + ") T on Z.STCD=T.STCD");
		INNER_JOIN("jssq.ST_RSVRFCCH_B R on Z.stcd = R.STCD");
		return SQL();
	}

	public String getLatestWeirGatesGisInfoSQL(Map<String, Date> maps) {
		maps = this.reParams(maps);
		
		BEGIN();
		String primaryTableSql = primaryTableBuild(DataType.WeirGatesGis,maps);
		String slaveTableSql = slaveTableBuild(DataType.WeirGatesGis);
		SELECT(columnToFieldMap(DataType.WeirGatesGis));
		FROM("(" + primaryTableSql + ") Z");
		INNER_JOIN("(" + slaveTableSql + ") T on Z.STCD=T.STCD");
		return SQL();
	}

	public String getLatestPumpingStationGisInfoSQL(Map<String, Date> maps) {
		maps = this.reParams(maps);
		
		BEGIN();
		String primaryTableSql = primaryTableBuild(DataType.PumpingStationGis,maps);
		String slaveTableSql = slaveTableBuild(DataType.PumpingStationGis);
		SELECT(columnToFieldMap(DataType.PumpingStationGis));
		FROM("(" + primaryTableSql + ") Z");
		INNER_JOIN("(" + slaveTableSql + ") T on Z.STCD=T.STCD");
		return SQL();
	}

	public String getLatestTideGisInfoSQL(Map<String, Date> maps) {
		maps = this.reParams(maps);

		BEGIN();
		String primaryTableSql = primaryTableBuild(DataType.TideGis,maps);
		String slaveTableSql = slaveTableBuild(DataType.TideGis);
		SELECT(columnToFieldMap(DataType.TideGis));
		FROM("(" + primaryTableSql + ") Z");
		INNER_JOIN("(" + slaveTableSql + ") T on Z.STCD=T.STCD");
		return SQL();
	}
	public String getLatestWaterGisInfo(Map<String, Date> maps){
		maps = this.reParams(maps);
		
		BEGIN();
		
		String bsql=this.columnToFieldMap(maps);
		String lastsql=this.columToLastFieldMap();
		SELECT(lastsql);
		FROM("("+ bsql +") B");
		LEFT_OUTER_JOIN(ST_RVFCCH_B+" C ON (B.stcd = C.stcd) and (B.sttp in ('ZZ','DD','DP','TT'))");
		LEFT_OUTER_JOIN(ST_RSVRFSR_B+" D ON (B.stcd = D.stcd) and (B.sttp in ('ST','SO','MS','BT','BO','OZ')) and #{md}>= D.bgmd and #{md} <= D.EDmd");
		ORDER_BY("\"sort\" desc nulls last");
		return SQL();
		
	}
	private String columToLastFieldMap(){
		StringBuffer sb = new StringBuffer();
		sb.append("B.LGTD,");
		sb.append("B.LTTD,");
		sb.append("B.STCD,");
		sb.append("B.STNM,");
		sb.append("B.inProv,");
		sb.append("B.sttp,");
		sb.append("B.val,");
		sb.append("B.dval,");
		sb.append("B.frgrd,");
		sb.append("B.tm,");
		sb.append("case when (B.sttp = 'ST') or (B.sttp = 'SO') or (B.sttp = 'MS') or (B.sttp = 'BT') or (B.sttp = 'BO') or (B.sttp = 'OZ') then B.val-D.FSLTDZ else B.val-C.WRZ end \"sort\",B.\"warnLevel\", ");
		sb.append("case when (B.sttp = 'ST') or (B.sttp = 'SO') or (B.sttp = 'MS') or (B.sttp = 'BT') or (B.sttp = 'BO') or (B.sttp = 'OZ') then D.FSLTDZ else C.WRZ end \"warValue\"");
		return sb.toString();
		
	}
	private String columnToFieldMap(Map<String, Date> maps){
		StringBuffer sb = new StringBuffer();
		String commonSql=commonTableSql(maps);
		String ot2Sql = ot2Sql(maps);
		String ot3Sql = ot3Sql(maps);
		sb.append("OT1.LGTD,");
		sb.append("OT1.LTTD,");
		sb.append("OT1.STCD,");
		sb.append("OT1.STNM,");
		sb.append("OT1.inProv,");
		sb.append("OT1.sttp,");
		sb.append("OT1.val,");
		sb.append("OT1.dval,");
		sb.append("OT1.frgrd,");
		sb.append("OT1.tm,");
		sb.append("case OT2.countHJ when 1 then '小洪水' when 2 then '中洪水' when 3 then '大洪水' when 4 then '特大洪水' else ' ' end || case OT3.countKJ when 1 then '轻度枯水' when 2 then '较重枯水' when 3 then '严重枯水' when 4 then '特别严重枯水' else ' ' end \"warnLevel\"");
		SELECT(sb.toString());
		FROM("("+ commonSql +") OT1");
		LEFT_OUTER_JOIN("("+ ot2Sql +") OT2 ON OT1.STCD = OT2.STCD");
		LEFT_OUTER_JOIN("("+ ot3Sql +") OT3 ON OT1.STCD = OT3.STCD");
		return SQL();
	}
	private String ot2Sql(Map<String, Date> maps){
		maps = this.reParams(maps);
		String commonSql=commonTableSql(maps);
		SELECT("T1.STCD");
		SELECT("count(*) countHJ");
		FROM("("+ commonSql +") T1");
		LEFT_OUTER_JOIN(ST_HJ_B +" T2 ON T1.STCD = T2.STCD");
		WHERE(" T1.val > T2.WZ ");
		GROUP_BY("T1.STCD");
		
		return SQL();
	}
	
	private String ot3Sql(Map<String, Date> maps){
		maps = this.reParams(maps);
		String commonSql=commonTableSql(maps);
		SELECT("T1.STCD");
		SELECT("count(*) countKJ");
		FROM("("+ commonSql +") T1");
		LEFT_OUTER_JOIN(ST_KJ_B+" T2 ON T1.STCD = T2.STCD");
		WHERE(" T1.val < T2.WZ ");
		GROUP_BY("T1.STCD");
		
		return SQL();
	}
	
	private String commonTableSql(Map<String, Date> maps){
		String riverTableSql = this.getLatestRiverGisInfoSQL(maps);
		String reservoirTableSql = this.getLatestReservoirGisInfoSQL(maps);
		String weirTableSql = this.getLatestWeirGatesGisInfoSQL(maps);
		String pumpTableSql = this.getLatestPumpingStationGisInfoSQL(maps);
		String tideTableSql = this.getLatestTideGisInfoSQL(maps);
		StringBuffer commonTableSql = new StringBuffer();
		commonTableSql.append(riverTableSql);
		commonTableSql.append(" union all ");
		commonTableSql.append(reservoirTableSql);
		commonTableSql.append(" union all ");
		commonTableSql.append(weirTableSql);
		commonTableSql.append(" union all ");
		commonTableSql.append(pumpTableSql);
		commonTableSql.append(" union all ");
		commonTableSql.append(tideTableSql);
		return commonTableSql.toString();
		
	}

	private String primaryTableBuild(DataType dataType,Map<String, Date> maps) {
		maps= this.reParams(maps);
		SELECT("STCD");
		SELECT("TM");
		switch (dataType) {
		case RiverGis:
			SELECT("Z");
			SELECT("null DZ");
			FROM(ST_RIVER_R);
			break;
		case ReservoirGis:
			SELECT("RZ");
			SELECT("null DZ");
			FROM(ST_RSVR_R);
			break;
		case WeirGatesGis:
			SELECT("UPZ");
			SELECT("DWZ");
			FROM(ST_WAS_R);
			break;
		case PumpingStationGis:
			SELECT("PPUPZ");
			SELECT("PPDWZ");
			FROM(ST_PUMP_R);
			break;
		case TideGis:
			SELECT("TDZ");
			SELECT("null DZ");
			FROM(ST_TIDE_R);
			break;
		}
		/*WHERE("TM>#{startDate}");*/
		WHERE("TM = #{endDate}");
		/*GROUP_BY("STCD");*/
		return SQL();
	}

	private String slaveTableBuild(DataType dataType) {
		SELECT("*");
		FROM(ST_STBPRP_B + " B");
		switch (dataType) {
		case RiverGis:
			WHERE("B.STTP IN('ZZ','ZQ','PP','DD')");
			break;
		case ReservoirGis:
			WHERE("B.STTP IN('RR')");
			break;
		case WeirGatesGis:
			WHERE("B.STTP IN('ZZ','DD')");
			break;
		case PumpingStationGis:
			WHERE("B.STTP IN('DP')");
			break;
		case TideGis:
			WHERE("B.STTP IN('TT','DD')");
			break;
		}
		WHERE("B.LGTD IS NOT NULL");
		WHERE("B.LTTD IS NOT NULL");
		WHERE("B.ADDVCD IS NOT NULL");

		return SQL();
	}

	private String columnToFieldMap(DataType dataType) {
		StringBuffer sb = new StringBuffer();
		sb.append("T.LGTD,");
		sb.append("T.LTTD,");
		sb.append("Z.STCD,");
		sb.append("T.STNM,");
		sb.append("T.ADDVCD inProv,");
		switch (dataType) {
		case RiverGis:
			sb.append("'ZZ' sttp,");
			sb.append("Z.Z val,");
			sb.append("Z.DZ dval,");
			break;
		case ReservoirGis:
			sb.append("case R.RSVRTP when '1' then 'ST' when '2' then 'SO' when '3' then 'MS' when '4' then 'BT' when '5' then 'BO' else 'OZ' end sttp,");
			sb.append("Z.RZ val,");
			sb.append("Z.DZ dval,");
			break;
		case WeirGatesGis:
			sb.append("'DD' sttp,");
			sb.append("Z.UPZ val,");
			sb.append("Z.DWZ dval,");
			break;
		case PumpingStationGis:
			sb.append("'DP' sttp,");
			sb.append("Z.PPUPZ val,");
			sb.append("Z.PPDWZ dval,");
			break;
		case TideGis:
			sb.append("'TT' sttp,");
			sb.append("Z.TDZ val,");
			sb.append("Z.DZ dval,");
			break;
		}
		sb.append("T.FRGRD frgrd,");
		sb.append("Z.TM tm");

		return sb.toString();
	}

	private Map<String, Date> reParams(Map<String, Date> maps) {
		Date datePoint = (Date) maps.get("datePoint");
		Date startDate = DateUtils.addDays(datePoint, -3);
		Date endDate = datePoint;

		maps.put("startDate", startDate);
		maps.put("endDate", endDate);
		/*maps.remove("datePoint");*/

		return maps;
	}
	private Map<String, Integer> reMD(Map<String, Date> maps ,Map<String, Integer> map){
		Date datePoint = maps.get("datePoint");
		Calendar calendar=Calendar.getInstance();
		calendar.setTime(datePoint);
		String md= String.valueOf(calendar.MONTH* 100)+String.valueOf(calendar.DAY_OF_WEEK + 1);
		map.put("md",Integer.parseInt(md));
		System.out.println(Integer.parseInt(md));
		return map;
	}
}
